package database.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import org.aspectj.weaver.ast.And;

import database.pojo.PlayerCollectSingerInfo;
import database.pojo.SingleSongScoreInfo;
import database.pojo.UserInfo;
import manager.rank.type.singSongNumInfo;

public class SingleSongScoreInfoDao{
	@SuppressWarnings("finally")
	public SingleSongScoreInfo insert(SingleSongScoreInfo sInfo, Connection conn ) throws SQLException {
		
		String sqlInsert = "insert into SingleSongScore(openId, songId, score,rank,machineId) values( ? , ? , ? ,?,?)";
		PreparedStatement pstmt = null;
		try{
			pstmt = conn.prepareStatement(sqlInsert);//相当于python操作数据库的游标
			pstmt.setString(1, sInfo.getOpenId());
			pstmt.setString(2, sInfo.getSongId());
			pstmt.setInt(3, sInfo.getScore());
			pstmt.setInt(4, sInfo.getRank());
			pstmt.setInt(5, sInfo.getMachineId());
			pstmt.executeUpdate();
		}
		finally{
			if(pstmt != null){
				pstmt.close();
			}
			return sInfo;
		}
		
		
	}
	
	@SuppressWarnings("finally")
	public int selectRank (String songId,int score,Connection conn) throws SQLException {
		String sqlSelect ="select score from SingleSongScore where songId = ? order by score desc ";
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		int rank =0;
		int selectScore = -1;
		try {
			pstmt =conn.prepareStatement(sqlSelect);
			pstmt.setString(1, songId);
			rs = pstmt.executeQuery();
			while(rs.next()){
				selectScore =rs.getInt("score");
				rank = rank +1;
				if(selectScore == score) {
					System.out.println("the score is :"+ score);
					System.out.println("THE RANK IS "+ rank);
					break;
					
				}
			}
		}catch (Exception e) {
			System.out.println("selectRank------Exception-- "+e);
		}finally{
			if(rs != null){
				rs.close();
			}
			if(pstmt != null){
				pstmt.close();
			}
			return rank;
		}
		
	}
	
	public boolean isExist(String openId,String songId, Connection conn) throws SQLException{
		String sqlSelect = "select * from SingleSongScore where openId = ? and songId = ?  ";
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try{
			pstmt = conn.prepareStatement(sqlSelect);
			pstmt.setString(1,openId);
			pstmt.setString(2,songId);
			rs = pstmt.executeQuery();
			if(rs.next())

				if(rs.getString("openId") != null)
					return true;
				else {
					return false;
				}
			else {
				 return false;
			}
		}
		finally{
			if(rs != null){
				rs.close();
			}
			if(pstmt != null){
				pstmt.close();
			}
		}
	}
	
	public boolean isExistSongId(String songId, Connection conn) throws SQLException{
		String sqlSelect = "select * from SingleSongScore where songId = ?  ";
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try{
			pstmt = conn.prepareStatement(sqlSelect);
			pstmt.setString(1,songId);
			rs = pstmt.executeQuery();
			if(rs.next())

				if(rs.getString("songId") != null)
					return true;
				else {
					return false;
				}
			else {
				 return false;
			}
		}
		finally{
			if(rs != null){
				rs.close();
			}
			if(pstmt != null){
				pstmt.close();
			}
		}
	}
	
	public SingleSongScoreInfo update(SingleSongScoreInfo sInfo , Connection conn) throws SQLException {
		
		String sqlUpdate = "update SingleSongScore set score = ?,rank = ? where openId = ? and songId = ? and machineId = ?";
		PreparedStatement pstmt = null;
		
		try {
			pstmt = conn.prepareStatement(sqlUpdate);
			pstmt.setInt(1, sInfo.getScore());
			pstmt.setInt(2, sInfo.getRank());
			pstmt.setString(3, sInfo.getOpenId());
			pstmt.setString(4, sInfo.getSongId());
			pstmt.setInt(5, sInfo.getMachineId());
			pstmt.executeUpdate();
			
		}catch (Exception e) {
			System.out.println(" update Exception" + e);
		} 
		finally {
			if(pstmt != null) {
				pstmt.close();
			}
		}
		return sInfo;
		
	}
	
public void updateRank(String openId ,String songId,int rank,Connection conn) throws SQLException {
		
		String sqlUpdate = "update SingleSongScore set rank = ? where openId = ? and songId = ? and rank = ?";
		PreparedStatement pstmt = null;
		
		try {
			pstmt = conn.prepareStatement(sqlUpdate);
			pstmt.setInt(1, rank+1);
			pstmt.setString(2, openId);
			pstmt.setString(3, songId);
			pstmt.setInt(4, rank);
			pstmt.executeUpdate();
			
		}catch (Exception e) {
			System.out.println(" update Exception" + e);
		} 
		finally {
			if(pstmt != null) {
				pstmt.close();
			}
		}
		
	}

	
public ArrayList<Integer> selectScoreByOpenIdAndSongId(String openId,String songId,Connection conn) throws SQLException {
		
		String sqlSelect = "select * from SingleSongScore where openId = ?  and songId = ? and rank <=200";
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		ArrayList<Integer> scoreList = new ArrayList<Integer>();
		System.out.println("get open id " + openId);
		try{
			pstmt = conn.prepareStatement(sqlSelect);
			pstmt.setString(1, openId);
			pstmt.setString(2, songId);
			rs = pstmt.executeQuery();
			while(rs.next()){
				//System.out.println("selectScoreByOpenIdAndSongId-------score--"+rs.getString("score"));
				scoreList.add(rs.getInt("score"));
				}
			}finally {
				if(pstmt != null){
					pstmt.close();
				}
			}
		
		return scoreList;
	}

public ArrayList<SingleSongScoreInfo> select(String songId,int number,Connection conn) throws SQLException {
	
	String sqlSelect = "select * from SingleSongScore  and songId = ? and rank <=?";
	PreparedStatement pstmt = null;
	ResultSet rs = null;
	ArrayList<SingleSongScoreInfo> scoreList = new ArrayList<SingleSongScoreInfo>();
	SingleSongScoreInfo scoreInfo = null;
	System.out.println("get open id " + songId);
	try{
		pstmt = conn.prepareStatement(sqlSelect);
		pstmt.setString(1, songId);
		pstmt.setInt(2, number);
		rs = pstmt.executeQuery();
		while(rs.next()){
			//System.out.println("selectScoreByOpenIdAndSongId-------score--"+rs.getString("score"));
			scoreInfo = new SingleSongScoreInfo();
			scoreInfo.setMachineId(rs.getInt("machineId"));
			scoreInfo.setOpenId(rs.getString("openId"));
			scoreInfo.setRank(rs.getInt("rank"));
			scoreInfo.setSongId(rs.getString("songId"));
			scoreInfo.setScore(rs.getInt("score"));
			scoreList.add(scoreInfo);
			}
		}finally {
			if(pstmt != null){
				pstmt.close();
			}
		}
	
	return scoreList;
}

@SuppressWarnings("finally")
public int selectMaxScore(String openId, String songId, Connection conn) throws SQLException {
	String SQLselect = "select max(score) from SingleSongScore where openId =? and songId = ? ";
	ResultSet rs = null;
	PreparedStatement pstmt = null;
	int maxScore = 0;
	try{
		pstmt = conn.prepareStatement(SQLselect);
		pstmt.setString(1, openId);
		pstmt.setString(2, songId);
		rs = pstmt.executeQuery();
		if(rs.next()) {
			maxScore =rs.getInt("max(score)");
		}
		}finally {
			if(pstmt != null){
				pstmt.close();
			}
			return maxScore;
		}
	// TODO Auto-generated method stub
	
}

public ArrayList<SingleSongScoreInfo> getAllOrderInfo(String songId, int number, Connection conn) throws SQLException {
	String sqlSelect = "select * from SingleSongScore where songId  = ?  order by score  desc limit ?; ";
	PreparedStatement pstmt = null;
	ResultSet rs = null;
	ArrayList<SingleSongScoreInfo> scoreList = new ArrayList<SingleSongScoreInfo>();
	SingleSongScoreInfo scoreInfo = null;
	int selectNum =0;
	System.out.println("get songId id " + songId);
	try{
		pstmt = conn.prepareStatement(sqlSelect);
		pstmt.setString(1, songId);
		pstmt.setInt(2, number);
		rs = pstmt.executeQuery();
		while(rs.next()){
			//System.out.println("selectScoreByOpenIdAndSongId-------score--"+rs.getString("score"));
			selectNum =selectNum + 1;
			scoreInfo = new SingleSongScoreInfo();
			scoreInfo.setMachineId(rs.getInt("machineId"));
			scoreInfo.setOpenId(rs.getString("openId"));
			scoreInfo.setRank(selectNum);
			scoreInfo.setSongId(rs.getString("songId"));
			scoreInfo.setScore(rs.getInt("score"));
			scoreList.add(scoreInfo);
			
			if(selectNum == number) {
				System.out.println("GETALLORDERINFO---------number--"+ selectNum);
				break;
			}
			}
		}finally {
			if(pstmt != null){
				pstmt.close();
			}
		}
	
	return scoreList;
}

public ArrayList<SingleSongScoreInfo> getOrderInfoByMid(String songId, int number, int rankRange, Connection conn) throws SQLException {
	String sqlSelect = "select * from SingleSongScore where machineId = ? and songId = ? order by score desc limit ? ";
	PreparedStatement pstmt = null;
	ResultSet rs = null;
	ArrayList<SingleSongScoreInfo> scoreList = new ArrayList<SingleSongScoreInfo>();
	SingleSongScoreInfo scoreInfo = null;
	int selectNum =0;
	System.out.println("get songId id " + songId);
	try{
		pstmt = conn.prepareStatement(sqlSelect);
		pstmt.setInt(1, rankRange);
		pstmt.setString(2, songId);
		pstmt.setInt(3, number);
		rs = pstmt.executeQuery();
		while(rs.next()){
			//System.out.println("selectScoreByOpenIdAndSongId-------score--"+rs.getString("score"));
			selectNum =selectNum + 1;
			scoreInfo = new SingleSongScoreInfo();
			scoreInfo.setMachineId(rs.getInt("machineId"));
			scoreInfo.setOpenId(rs.getString("openId"));
			scoreInfo.setRank(selectNum);
			scoreInfo.setSongId(rs.getString("songId"));
			scoreInfo.setScore(rs.getInt("score"));
			scoreList.add(scoreInfo);
			
			if(selectNum == number) {
				System.out.println("GETALLORDERINFO---------number--"+ selectNum);
				break;
			}
			}
		}finally {
			if(pstmt != null){
				pstmt.close();
			}
		}
	
	return scoreList;
}

public ArrayList<singSongNumInfo> getAllOrderNum(int number, Connection conn) throws SQLException {
	String sqlSelect = "select openId, count(songId) from SingleSongScore group by openId  order by count(songId) desc limit ? ";
	PreparedStatement pstmt = null;
	ResultSet rs = null;
	ArrayList<singSongNumInfo> scoreList = new ArrayList<singSongNumInfo>();
	singSongNumInfo sSInfo = null;
	int selectNum =0;
	try{
		pstmt = conn.prepareStatement(sqlSelect);
		pstmt.setInt(1, number);
		rs = pstmt.executeQuery();
		while(rs.next()){
			//System.out.println("selectScoreByOpenIdAndSongId-------score--"+rs.getString("score"));
			selectNum =selectNum + 1;
			sSInfo = new singSongNumInfo();
			sSInfo.setOpenId(rs.getString("openId"));
			sSInfo.setRank(selectNum);
			sSInfo.setNumber(rs.getInt("count(songId)"));
			scoreList.add(sSInfo);
			
			if(selectNum == number) {
				System.out.println("GETALLORDERINFO---------number--"+ selectNum);
				break;
			}
			}
		}finally {
			if(pstmt != null){
				pstmt.close();
			}
		}
	
	return scoreList;
}

public ArrayList<singSongNumInfo> getOrderNumByMid(int number, int rankRange, Connection conn) throws SQLException {
	String sqlSelect = "select openId, count(songId) from SingleSongScore where machineId = ? group by openId  order by count(songId) desc limit ? ";
	PreparedStatement pstmt = null;
	ResultSet rs = null;
	ArrayList<singSongNumInfo> scoreList = new ArrayList<singSongNumInfo>();
	singSongNumInfo sSInfo = null;
	int selectNum =0;
	try{
		pstmt = conn.prepareStatement(sqlSelect);
		pstmt.setInt(1, rankRange);
		pstmt.setInt(2, number);
		rs = pstmt.executeQuery();
		while(rs.next()){
			//System.out.println("selectScoreByOpenIdAndSongId-------score--"+rs.getString("score"));
			selectNum =selectNum + 1;
			sSInfo = new singSongNumInfo();
			sSInfo.setOpenId(rs.getString("openId"));
			sSInfo.setRank(selectNum);
			sSInfo.setNumber(rs.getInt("count(songId)"));
			scoreList.add(sSInfo);
			
			if(selectNum == number) {
				System.out.println("GETALLORDERINFO---------number--"+ selectNum);
				break;
			}
			}
		}finally {
			if(pstmt != null){
				pstmt.close();
			}
		}
	
	return scoreList;
}

public int getAllTotalNumOfSongId(String songId, Connection conn) throws SQLException {
	String SQLselect = "select count(songId) from SingleSongScore where songId =? ";
	ResultSet rs = null;
	PreparedStatement pstmt = null;
	int totalNum = 0;
	try{
		pstmt = conn.prepareStatement(SQLselect);
		pstmt.setString(1, songId);
		rs = pstmt.executeQuery();
		if(rs.next()) {
			totalNum =rs.getInt("count(songId)");
		}
		}finally {
			if(pstmt != null){
				pstmt.close();
			}
		}
	return totalNum;
}

public int getTotalNumOfSongId(String songId, int rankRange, Connection conn) throws SQLException {
	String SQLselect = "select count(songId) from SingleSongScore where songId =? and machineId =?";
	ResultSet rs = null;
	PreparedStatement pstmt = null;
	int totalNum = 0;
	try{
		pstmt = conn.prepareStatement(SQLselect);
		pstmt.setString(1, songId);
		pstmt.setInt(2, rankRange);
		rs = pstmt.executeQuery();
		if(rs.next()) {
			totalNum =rs.getInt("count(songId)");
		}
		}finally {
			if(pstmt != null){
				pstmt.close();
			}
		}
	return totalNum;
}

@SuppressWarnings("finally")
public int selectAllNumByOpenId(String openId, Connection conn) throws SQLException {
	String SQLselect = "select count(openId) from SingleSongScore where openId =? ";
	ResultSet rs = null;
	PreparedStatement pstmt = null;
	int singNum = 0;
	try{
		pstmt = conn.prepareStatement(SQLselect);
		pstmt.setString(1, openId);
		rs = pstmt.executeQuery();
		if(rs.next()) {
			 singNum =rs.getInt("count(openId)");
		}
		}finally {
			if(pstmt != null){
				pstmt.close();
			}
			return singNum;
		}
	
}

public int selectNumByOpenId(String openId, int rankRange, Connection conn) throws SQLException {
	String SQLselect = "select count(openId) from SingleSongScore where openId =? and machineId =?";
	ResultSet rs = null;
	PreparedStatement pstmt = null;
	int singNum = 0;
	try{
		pstmt = conn.prepareStatement(SQLselect);
		pstmt.setString(1, openId);
		pstmt.setInt(2, rankRange);
		rs=pstmt.executeQuery();
		if(rs.next()) {
			singNum =rs.getInt("count(openId)");
		}
		}finally {
			if(pstmt != null){
				pstmt.close();
			}
		}
	return singNum;
}

	
	
}